﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
//using bookDbClass;

namespace tsgl
{
    class bibliothecaClass
    {
        public static DataTable getBibliothecaDataAll()
        {
            DataTable dt = bookDbClass.ExecuteDataTable("select 书目编号,图书名称,作者,出版社名称,ISBN,出版日期,图书页数,价格,图书类型名称,总藏书数量,现存数量,馆藏地点,简介 from bibliothecaView");
            return dt;
        }

        public static DataTable getBibliothecaData(string bibliothecaNo)
        {
            return bookDbClass.ExecuteDataTable("select * from bibliothecaView where 书目编号=@a", new SqlParameter("a", bibliothecaNo));
        }

        public static DataTable getBookTypeNameAll()
        {
            return bookDbClass.ExecuteDataTable("select 图书类型名称 from 图书类型");
        }

        public static DataTable getBookTypeId(string bookType)
        {
            return bookDbClass.ExecuteDataTable("select 图书类型代码 from 图书类型 where 图书类型名称 = @a",new SqlParameter("a",bookType));
        }

        public static DataTable getPublisherName()
        {
            return bookDbClass.ExecuteDataTable("select 出版社名称 from 出版社");
        }

        public static DataTable getPublisherISBN(string publisher)
        {
            return bookDbClass.ExecuteDataTable("select ISBN from 出版社 where 出版社名称 = @a",new SqlParameter("a",publisher));
        }

        public static DataTable getDepositary()
        {
            return bookDbClass.ExecuteDataTable("select 馆藏地点编号 from 馆藏地点");
        }

        public static Boolean bibliothecaAdd(string bibliothecaID, string bibliothecaName, string author, string publisherISBN, string ISBN, string publisherDate, int pages, Single price, string bookType, int nowNums, string place, string synopsis, int inNums)
        {
            //string bibliothecaID, string bibliothecaName, string author, string publisherISBN, string ISBN, string publisherDate, int pages, Single price, string bookType, int nowNums, string place, string synopsis, int inNums
            try
            {
                bookDbClass.ExecuteNonQurey("insert into 书目信息(书目编号,图书名称,作者,出版社,ISBN,出版日期,图书页数,价格,图书类型,现存数量,馆藏地点,简介,待入库数量) values(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m)", new SqlParameter("a", bibliothecaID), new SqlParameter("b", bibliothecaName), new SqlParameter("c", author), new SqlParameter("d", publisherISBN), new SqlParameter("e", ISBN), new SqlParameter("f", publisherDate), new SqlParameter("g", pages), new SqlParameter("h", price), new SqlParameter("i", bookType), new SqlParameter("j", nowNums), new SqlParameter("k", place), new SqlParameter("l", synopsis), new SqlParameter("m", inNums));
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static Boolean bibliothecaEditAll(string bibliothecaID, string bibliothecaName, string author, string publisherISBN, string ISBN, string publisherDate, int pages, Single price, string bookType, int totalNums, int nowNums, string place, string synopsis, string keyField)
        {
            try
            {
                bookDbClass.ExecuteNonQurey("update 书目信息 set 书目编号=@a,图书名称=@b,作者=@c,出版社=@d,ISBN=@e,出版日期=@f,图书页数=@g,价格=@h,图书类型=@i,总藏书数量=@j,现存数量=@k,馆藏地点=@l,简介=@m where 书目编号=@n", new SqlParameter("a", bibliothecaID), new SqlParameter("b", bibliothecaName), new SqlParameter("c", author), new SqlParameter("d", publisherISBN), new SqlParameter("e", ISBN), new SqlParameter("f", publisherDate), new SqlParameter("g", pages), new SqlParameter("h", price), new SqlParameter("i", bookType), new SqlParameter("j", totalNums), new SqlParameter("k", nowNums), new SqlParameter("l", place), new SqlParameter("m", synopsis), new SqlParameter("n", keyField));
                return true;           
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static Boolean bibliothecaEditPart(string bibliothecaID, string bibliothecaName, string author, string publisherISBN, string ISBN, string publisherDate, int pages, Single price, string bookType, int totalNums, int nowNums, string place, string synopsis)
        {
            try
            {
                bookDbClass.ExecuteNonQurey("update 书目信息 set 图书名称=@b,作者=@c,出版社=@d,ISBN=@e,出版日期=@f,图书页数=@g,价格=@h,图书类型=@i,总藏书数量=@j,现存数量=@k,馆藏地点=@l,简介=@m where 书目编号=@a", new SqlParameter("b", bibliothecaName), new SqlParameter("c", author), new SqlParameter("d", publisherISBN), new SqlParameter("e", ISBN), new SqlParameter("f", publisherDate), new SqlParameter("g", pages), new SqlParameter("h", price), new SqlParameter("i", bookType), new SqlParameter("j", totalNums), new SqlParameter("k", nowNums), new SqlParameter("l", place), new SqlParameter("m", synopsis), new SqlParameter("a", bibliothecaID));
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public void bibliothecaDelete(string bibliothecaID)
        {
            bookDbClass.ExecuteNonQurey("delete from 书目信息 where 书目编号=@a", new SqlParameter("a", bibliothecaID));
        }




        public static DataTable getBorrowerInfoAll()
        {
           return bookDbClass.ExecuteDataTable("select * from borrowerView");
        }

        public static DataTable getBorrowerInfor(string borrowerNo)
        {
           return bookDbClass.ExecuteDataTable("select * from borrowerView where 借阅者编号=@a",new SqlParameter("a",borrowerNo));
        }

        public static DataTable getBorrowerType()
        {
            return bookDbClass.ExecuteDataTable("select 借阅者类型 from 借阅者类型");
        }

        public static DataTable getBorrowerTypeInfo(string borrowerType)
        {
            return bookDbClass.ExecuteDataTable("select 类型编号 from 借阅者类型 where 借阅者类型 =@a ",new SqlParameter("a",borrowerType));
        }

        public static DataTable getDepartmentName()
        {
            return bookDbClass.ExecuteDataTable("select 部门名称 from 部门");
        }

        public static DataTable getDepartmentId(string departmentName)
        {
            return bookDbClass.ExecuteDataTable("select 部门编号 from 部门 where 部门名称=@a",new SqlParameter("a",departmentName));
        }

        public static Boolean borrowerAdd(string borrowerNo,string borrowerName,string borrowerSex,DateTime borthday,int borrowerType,string cardState,DateTime cardDate,DateTime cardLineDate,string cardId,string Tel,string department,float leftCash)
        {
            try
            {
                bookDbClass.ExecuteNonQurey("insert into 借阅者信息(借阅者编号,姓名,性别,出生日期,借阅者类型,借书证状态,办证日期,有效期截止日期,证件号码,联系电话,部门,押金剩余) values(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l)", new SqlParameter("a", borrowerNo), new SqlParameter("b", borrowerName), new SqlParameter("c", borrowerSex), new SqlParameter("d", borthday), new SqlParameter("e", borrowerType), new SqlParameter("f", cardState), new SqlParameter("g", cardDate), new SqlParameter("h", cardLineDate), new SqlParameter("i", cardId), new SqlParameter("j", Tel), new SqlParameter("k", department), new SqlParameter("l", leftCash));
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static Boolean borrowerEditAll(string borrowerNo, string borrowerName, string borrowerSex, DateTime borthday, int borrowerType, string cardState, DateTime cardDate, DateTime cardLineDate, string cardId, string Tel, string department, float leftCash, string keyField)
        {
            try
            {
                bookDbClass.ExecuteNonQurey("update 借阅者信息 set 借阅者编号=@a,姓名=@b,性别=@c,出生日期=@d,借阅者类型=@e,借书证状态=@f,办证日期=@g,有效期截止日期=@h,证件号码=@i,联系电话=@j,部门=@k,押金剩余=@l where 借阅者编号=@m ", new SqlParameter("a", borrowerNo), new SqlParameter("b", borrowerName), new SqlParameter("c", borrowerSex), new SqlParameter("d", borthday), new SqlParameter("e", borrowerType), new SqlParameter("f", cardState), new SqlParameter("g", cardDate), new SqlParameter("h", cardLineDate), new SqlParameter("i", cardId), new SqlParameter("j", Tel), new SqlParameter("k", department), new SqlParameter("l", leftCash),new SqlParameter("m",keyField));
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static Boolean borrowerEditPart(string borrowerName, string borrowerSex, DateTime borthday, int borrowerType, string cardState, DateTime cardDate, DateTime cardLineDate, string cardId, string Tel, string department, float leftCash,string borrowerNo)
        {
            try
            {
                bookDbClass.ExecuteNonQurey("update 借阅者信息 set 姓名=@b,性别=@c,出生日期=@d,借阅者类型=@e,借书证状态=@f,办证日期=@g,有效期截止日期=@h,证件号码=@i,联系电话=@j,部门=@k,押金剩余=@l where 借阅者编号=@a", new SqlParameter("b", borrowerName), new SqlParameter("c", borrowerSex), new SqlParameter("d", borthday), new SqlParameter("e", borrowerType), new SqlParameter("f", cardState), new SqlParameter("g", cardDate), new SqlParameter("h", cardLineDate), new SqlParameter("i", cardId), new SqlParameter("j", Tel), new SqlParameter("k", department), new SqlParameter("l", leftCash), new SqlParameter("a", borrowerNo));
                return true;
            }
            catch (Exception)
            {
               return false;
            }
        }
    }
}
